package de.stalinski.db;

import de.stalinski.logging.FrbrLogger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

public class PricesTableConnector {

    private static PricesTableConnector PricesTableConnector;
    private FrbrLogger logger = FrbrLogger.instance();
    private static String sUsr = "root";
    private static String sPwd = "admin";
    private static String sDB = "frbrUsers";
    private static String sTable = "prices_drinks";
    private static Connection connection;
    private static String sHost = "localhost";
    private static String sConn = "jdbc:mysql://" + sHost + ":3306/" + sDB;

    public PricesTableConnector() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
        try {
            connection = DriverManager.getConnection(sConn, sUsr, sPwd);
            System.out.println(connection != null);
        } catch (SQLException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
    }

    public ArrayList<String> getSorts() throws SQLException {
        ArrayList<String> resultSet = new ArrayList();
        Statement st = null;
        ResultSet rs = null;
        st = connection.createStatement();
        rs = st.executeQuery("select SORT from " + sTable);
        ResultSetMetaData rsmd = rs.getMetaData();
        int i;
        int n = rsmd.getColumnCount();
        while (rs.next()) {
            resultSet.add(rs.getString("SORT"));
        }
        return resultSet;
    }

    public HashMap<String, Double> getPricesAsHash() throws SQLException {
        HashMap<String, Double> resultSet = new HashMap();
        Statement st = null;
        ResultSet rs = null;
        st = connection.createStatement();
        rs = st.executeQuery("select * from " + sTable);
        ResultSetMetaData rsmd = rs.getMetaData();
        int i;
        int n = rsmd.getColumnCount();
        while (rs.next()) {
            resultSet.put(rs.getString("SORT"), Double.parseDouble(rs.getString("VALUE")));
        }
        return resultSet;
    }

    public String getPrices() {
        String out = "<table class=\"Prices\"><thead>";
        try {
            Statement st = null;
            ResultSet rs = null;
            st = connection.createStatement();
            rs = st.executeQuery("select * from " + sTable);
            // Get meta data:
            ResultSetMetaData rsmd = rs.getMetaData();

            int i;
            int n = rsmd.getColumnCount();
            for (int a = 1; a <= rsmd.getColumnCount(); a++) {
                out += "<th>" + rsmd.getColumnName(a) + "</th>";
            }
            out += "<th>WERT</th>";

            out += "<tbody>";
            while (rs.next()) {
                out += "<tr>";
                for (i = 1; i <= n; i++) {
                    out += "<td>" + rs.getString(i) + "</td>";
                }
                Double value = Double.parseDouble(rs.getString("VALUE")) * Double.parseDouble(rs.getString("STOCK"));
                value = Math.round(value * 100.0) / 100.0;
                out += "<td>" + value + "</td>";
                out += "</tr>";
            }
        } catch (SQLException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
        return out + "</tbody></thead></table>";
    }
}

